/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Student;

import Academic.AcademicBean;
import Address.AddressBean;
import Admission.AdmissionBean;
import Category.CategoryBean;
import Country.CountryBean;
import Course.CourseBean;
import Documents.StudentDocumentBean;
import Facility.FacilityBean;
import Parents.ParentsBean;
import State.StateBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {

    static Connection con = null;

    public static int add(StudentBean sb) throws SQLException {
        int i = 0, k = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into student values(studentsid.nextval,?,?,?,?,sysdate,?,?,?,?)");
            pre.setInt(1, sb.getAdmissionId());
            pre.setString(2, sb.getStudentName());
            pre.setString(3, sb.getGender());
            pre.setInt(4, sb.getAddressId());
            pre.setInt(5, sb.getCategoryId());
            pre.setString(6, sb.getPhotoName());
            pre.setString(7, sb.getEntryEmail());
            pre.setDate(8, sb.getDob());
            i = pre.executeUpdate();
            if (i != 0) {
                k = getMaxId();
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return k;
    }

    public static int getMaxId() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select max(student_id) from student");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                i = rs.getInt(1);;
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return i;
    }

    public static StudentBean viewSpecific(int val) throws SQLException {
        StudentBean sb = new StudentBean();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from student left join ADMISSION on student.ADMISSION_ID=ADMISSION.ADMISSION_ID left join course on ADMISSION.course_id=course.course_id left join address on student.ADDRESS_ID=address.ADDRESS_ID left join state on address.state_id=state.state_id left join country on address.country_id=country.country_id left join CATEGORY on student.CATEGORY_ID=category.CATEGORY_ID where student.admission_id=?");
            pre.setInt(1, val);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                AdmissionBean adb = new AdmissionBean();
                AddressBean ad = new AddressBean();
                CategoryBean cab = new CategoryBean();
                CourseBean cb = new CourseBean();
                CountryBean cob = new CountryBean();
                StateBean stb = new StateBean();
                sb.setStudentId(rs.getInt(1));
                sb.setAdmissionId(rs.getInt(2));
                sb.setStudentName(rs.getString(3));
                sb.setGender(rs.getString(4));
                sb.setAddressId(rs.getInt(5));
                sb.setEntryDate(rs.getDate(6));
                sb.setCategoryId(rs.getInt(7));
                sb.setPhotoName(rs.getString(8));
                sb.setEntryEmail(rs.getString(9));
                sb.setDob(rs.getDate(10));
                adb.setAddmissionId(rs.getInt(11));
                adb.setStudentEmail(rs.getString(12));
                adb.setStudentMobile(rs.getString(13));
                adb.setRollNumber(rs.getString(14));
                adb.setStudentSession(rs.getString(15));
                adb.setCourseId(rs.getInt(16));
                adb.setApplicationNo(rs.getString(17));
                adb.setAdmissionDate(rs.getDate(18));
                adb.setEntryDate(rs.getDate(19));
                adb.setEntryEmail(rs.getString(20));
                adb.setSemester(rs.getInt(21));
                cb.setCourseId(rs.getInt(22));
                cb.setCourseName(rs.getString(23));
                cb.setCourseType(rs.getInt(24));
                cb.setCoursePeriod(rs.getInt(25));
                cb.setStream(rs.getString(26));
                cb.setEntryDate(rs.getDate(27));
                adb.setCourseBean(cb);
                sb.setAdmissionBean(adb);
                ad.setAddressId(rs.getInt(28));
                ad.setAddress1(rs.getString(29));
                ad.setAddress2(rs.getString(30));
                ad.setCountryId(rs.getInt(31));
                ad.setStateId(rs.getInt(32));
                ad.setPinCode(rs.getString(33));
                ad.setEntryDate(rs.getDate(34));

                stb.setStateId(rs.getInt(35));
                stb.setCountryId(rs.getInt(36));
                stb.setStateName(rs.getString(37));
                stb.setEntryDate(rs.getDate(38));
                ad.setStateBean(stb);

                cob.setCountryId(rs.getInt(39));
                cob.setCountryName(rs.getString(40));
                cob.setEntryDate(rs.getDate(41));
                ad.setCountryBean(cob);
                sb.setAddressBean(ad);
                cab.setCategoryId(rs.getInt(42));
                cab.setCategory(rs.getString(43));
                cab.setEntryDate(rs.getDate(44));
                sb.setCategoryBean(cab);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return sb;
    }

    /* public static Vector viewAll() throws SQLException {
     Vector<Object> list = new Vector<Object>();

     try {
     con = DBConnection.Connection.getConnection();
     PreparedStatement pre = con.prepareStatement("select * from state left join country on country.country_id=state.country_id");
     ResultSet rs = pre.executeQuery();
     while (rs.next()) {
     StateBean sb = new StateBean();
     CountryBean cb = new CountryBean();
     sb.setStateId(rs.getInt(1));
     sb.setCountryId(rs.getInt(2));
     sb.setStateName(rs.getString(3));
     sb.setEntryDate(rs.getDate(4));
     cb.setCountryId(rs.getInt(5));
     cb.setCountryName(rs.getString(6));
     sb.setCountryBean(cb);
     list.add(sb);
     }
     } catch (Exception e) {
     System.out.println(e);
     } finally {
     con.close();
     }
     return list;
     }

     public static int edit(StateBean sb) throws SQLException {
     int i = 0;
     try {
     con = DBConnection.Connection.getConnection();
     PreparedStatement pre = con.prepareStatement("update state set country_id=?, state_name=?,entrydate=sysdate where state_id=?");
     pre.setInt(1, sb.getCountryId());
     pre.setString(2, sb.getStateName());
     pre.setInt(3, sb.getStateId());
     i = pre.executeUpdate();

     } catch (Exception e) {
     System.out.println(e);
     } finally {
     con.close();
     }
     return i;
     }*/
    public static int edit(StudentBean sb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update student set ADMISSION_ID=?,STUDENT_NAME=?,GENDER=?,ADDRESS_ID=?,ENTRY_DATE=sysdate,CATEGORY_ID=?,PHOTO_NAME=?,ENTRY_EMAIL=? where STUDENT_ID=?");
            pre.setInt(1, sb.getAdmissionId());
            pre.setString(2, sb.getStudentName());
            pre.setString(3, sb.getGender());
            pre.setInt(4, sb.getAddressId());
            pre.setInt(5, sb.getCategoryId());
            pre.setString(6, sb.getPhotoName());
            pre.setString(7, sb.getEntryEmail());
            pre.setInt(8, sb.getStudentId());
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static String viewName(int id) throws SQLException {
        String name = "";
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select STUDENT_NAME from STUDENT where ADMISSION_ID=?");
            pre.setInt(1, id);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                name = rs.getString(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return name;
    }

    public static int chkStudent(int admissionId, int studentId) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from STUDENT where ADMISSION_ID=? or student_id=?");
            pre.setInt(1, admissionId);
            pre.setInt(2, studentId);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                i = 1;
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }
    public static StudentBean viewSpecific(String val) throws SQLException {
        StudentBean sb = new StudentBean();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from student s left join ADMISSION a on s.ADMISSION_ID=a.ADMISSION_ID left join course c on a.course_id=c.course_id left join address ad on s.ADDRESS_ID=ad.ADDRESS_ID left join state  st on ad.state_id=st.state_id left join country co on ad.country_id=co.country_id left join CATEGORY ca on s.CATEGORY_ID=ca.CATEGORY_ID left join PARENTS p on p.ADMISSION_ID=a.ADMISSION_ID left join ACADEMIC ac on ac.ADMISSION_ID=a.ADMISSION_ID left join FACILITY f on f.ADMISSION_ID=a.ADMISSION_ID where a.STUDENT_EMAIL=?");
            pre.setString(1, val);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                AdmissionBean adb = new AdmissionBean();
                AddressBean ad = new AddressBean();
                CategoryBean cab = new CategoryBean();
                CourseBean cb = new CourseBean();
                CountryBean cob = new CountryBean();
                StateBean stb = new StateBean();
                ParentsBean pb=new ParentsBean();
                AcademicBean acb=new AcademicBean();
                //StudentDocumentBean sd=new StudentDocumentBean();
                FacilityBean fb=new FacilityBean();
                
                sb.setStudentId(rs.getInt(1));
                sb.setAdmissionId(rs.getInt(2));
                sb.setStudentName(rs.getString(3));
                sb.setGender(rs.getString(4));
                sb.setAddressId(rs.getInt(5));
                sb.setEntryDate(rs.getDate(6));
                sb.setCategoryId(rs.getInt(7));
                sb.setPhotoName(rs.getString(8));
                sb.setEntryEmail(rs.getString(9));
                sb.setDob(rs.getDate(10));
                adb.setAddmissionId(rs.getInt(11));
                adb.setStudentEmail(rs.getString(12));
                adb.setStudentMobile(rs.getString(13));
                adb.setRollNumber(rs.getString(14));
                adb.setStudentSession(rs.getString(15));
                adb.setCourseId(rs.getInt(16));
                adb.setApplicationNo(rs.getString(17));
                adb.setAdmissionDate(rs.getDate(18));
                adb.setEntryDate(rs.getDate(19));
                adb.setEntryEmail(rs.getString(20));
                adb.setSemester(rs.getInt(21));
                cb.setCourseId(rs.getInt(22));
                cb.setCourseName(rs.getString(23));
                cb.setCourseType(rs.getInt(24));
                cb.setCoursePeriod(rs.getInt(25));
                cb.setStream(rs.getString(26));
                cb.setEntryDate(rs.getDate(27));
                adb.setCourseBean(cb);
                
                ad.setAddressId(rs.getInt(28));
                ad.setAddress1(rs.getString(29));
                ad.setAddress2(rs.getString(30));
                ad.setCountryId(rs.getInt(31));
                ad.setStateId(rs.getInt(32));
                ad.setPinCode(rs.getString(33));
                ad.setEntryDate(rs.getDate(34));

                stb.setStateId(rs.getInt(35));
                stb.setCountryId(rs.getInt(36));
                stb.setStateName(rs.getString(37));
                stb.setEntryDate(rs.getDate(38));
                ad.setStateBean(stb);

                cob.setCountryId(rs.getInt(39));
                cob.setCountryName(rs.getString(40));
                cob.setEntryDate(rs.getDate(41));
                ad.setCountryBean(cob);
                sb.setAddressBean(ad);
                cab.setCategoryId(rs.getInt(42));
                cab.setCategory(rs.getString(43));
                cab.setEntryDate(rs.getDate(44));
                sb.setCategoryBean(cab);
                pb.setParentId(rs.getInt(45));
                pb.setFatherName(rs.getString(46));
                pb.setFatherMobileNo(rs.getString(47));
                pb.setMotherName(rs.getString(48));
                pb.setMotherMobileNo(rs.getString(49));
                pb.setFatherOccupation(rs.getString(50));
                pb.setMotherOccupation(rs.getString(51));
                pb.setGuardianName(rs.getString(52));
                pb.setRelation(rs.getString(53));
                pb.setGuardianOccupation(rs.getString(54));
                pb.setGuardianMobileNo(rs.getString(55));
                pb.setEntryDate(rs.getDate(56));
                pb.setAddressId(rs.getInt(57));
                pb.setAdmissionId(rs.getInt(58));
                pb.setEntryEmail(rs.getString(59));
                adb.setParentsBean(pb);
                acb.setAcademicId(rs.getInt(60));
                acb.setAddissionId(rs.getInt(61));
                acb.setHighSchool(rs.getString(62));
                acb.setHighBoardName(rs.getString(63));
                acb.setHighMaxMarks(rs.getInt(64));
                acb.setHighMarksObtained(rs.getInt(65));
                acb.setInterSchool(rs.getString(66));
                acb.setInterBoardName(rs.getString(67));
                acb.setInterMaxMarks(rs.getInt(68));
                acb.setInterMarksObtained(rs.getInt(69));
                acb.setOther(rs.getString(70));
                acb.setOtherBoardName(rs.getString(71));
                acb.setOtherMaxMarks(rs.getInt(72));
                acb.setOhterMarksObtained(rs.getInt(73));
                acb.setEntryDate(rs.getDate(74));
                adb.setAcademicBean(acb);
                fb.setFacilityId(rs.getInt(75));
                fb.setTransportationStatus(rs.getInt(76));
                fb.setHostelStatus(rs.getInt(77));
                fb.setMessStatus(rs.getInt(78));
                fb.setAdmissionId(rs.getInt(79));
                fb.setEntryEmail(rs.getString(80));
                adb.setFacilityBean(fb);
                sb.setAdmissionBean(adb);
                
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return sb;
    }
}
